#/bin/bash
#########################################################################################
#########################################################################################

dataFile=$1

host='10.61.13.96'
user='root'
password='mysql'
charset='--default-character-set=utf8'
dbname='recommendationengine_dev'

createStmt="drop table if exists category_raw; CREATE TABLE category_raw (id int(11) NOT NULL DEFAULT '0', pid int(11) DEFAULT NULL, name varchar(32) DEFAULT NULL, code varchar(128) DEFAULT NULL, idpath varchar(64) DEFAULT NULL, namepath varchar(64) DEFAULT NULL, PRIMARY KEY (id), KEY pid (pid)) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
loadStmt="load data local infile '$dataFile' into table category_raw character set utf8 columns terminated by ','"
replaceStmt="truncate table category; replace into category select c.id, c.pid, c.code, concat_ws('/', pc.name, c.name) name, now(), now(), 0 from category_raw c, category_raw pc where c.pid=pc.id"
truncateStmt="truncate table categoryprogram"

echo '[info] executing creating statement...'
mysql -u$user -p$password -h$host $charset $dbname -e"$createStmt"
echo '[info] executing loading statement...'
mysql -u$user -p$password -h$host $charset $dbname -e"$loadStmt"
echo '[info] executing replacing statement...'
mysql -u$user -p$password -h$host $charset $dbname -e"$replaceStmt"
echo '[info] executing truncating statement, truncating table categoryprogram...'
mysql -u$user -p$password -h$host $charset $dbname -e"$truncateStmt"

echo '[info] DONE!!!'

